﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.Common;

namespace DAL
{
    public class ProductKeyDAL : AbstractDAL<ProductKey>
    {
        static readonly string INSERT = "insert into ProductKey values(null,@PID,@KID);select last_insert_rowid() newid;";
        static readonly string UPDATE = "update ProductKey set PID=@PID,KID=@KID where id=@ID";
        static readonly string DELETE = "delete from ProductKey where id=@ID";
        static readonly string DELETE_DeleteAllByPID = "delete from ProductKey where PID=@PID";
        static readonly string SELECT = "select * from  ProductKey where id=@ID";
        static readonly string SELECT_ALL = "select * from  ProductKey";
        static readonly string SELECT_ALL_BY_PID = "select a.*,b.Name from  ProductKey a left join Key b on a.KID=b.ID where PID=@PID";

        protected override string InsertCommand
        {
            get
            {
                return INSERT;
            }
        }

        protected override string UpdateCommand
        {
            get { return UPDATE; }
        }

        protected override string DeleteCommand
        {
            get { return DELETE; }
        }

        protected override string SelectCommand
        {
            get { return SELECT; }
        }

        protected override string SelectAllCommand
        {
            get { return SELECT_ALL; }
        }

        protected override DbParameter[] GetInsertParameter(ProductKey model)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@PID", model.PID), SqlHelper.CreateParameter("@KID", model.KID) };
        }

        protected override void SyncID(ProductKey model, object scalar)
        {
            model.ID = (long)scalar;
        }

        protected override DbParameter[] GetUpdateParameter(ProductKey model)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@PID", model.PID), SqlHelper.CreateParameter("@KID", model.KID) };
        }

        protected override DbParameter[] GetDeleteParameter(ProductKey model)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@ID", model.ID) };
        }

        protected override DbParameter[] GetSeleteParameter(long id)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@ID", id) };
        }

        protected override ProductKey GetModel(DbDataReader reader)
        {
            ProductKey entity = new ProductKey();
            entity.ID = (long)reader["ID"];
            entity.PID = (long)reader["PID"];
            entity.KID = (long)reader["KID"];
            return entity;
        }

        protected ProductKeyView GetProductKeyViewModel(DbDataReader reader)
        {
            ProductKeyView entity = new ProductKeyView();
            entity.ID = (long)reader["ID"];
            entity.PID = (long)reader["PID"];
            entity.KID = (long)reader["KID"];
            entity.Name = (string)reader["Name"];
            return entity;
        }
        
        public void DeleteAllByPID(long pid)
        {
            DbCommand command = null;
            try
            {
                command = SqlHelper.GetCommand(DELETE_DeleteAllByPID);
                DbParameter parameters = SqlHelper.CreateParameter("@PID", pid);
                command.Parameters.Add(parameters);
                command.ExecuteNonQuery();
            }
            finally
            {
                SqlHelper.Close(command);
            }
        }

        public List<ProductKeyView> GetAllByPID(long pid)
        {
            List<ProductKeyView> list = new List<ProductKeyView>();
            DbDataReader reader = null;
            DbCommand command = null;
            try
            {
                command = SqlHelper.GetCommand(SELECT_ALL_BY_PID);
                command.Parameters.Add(SqlHelper.CreateParameter("@PID", pid));
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(GetProductKeyViewModel(reader));
                }
            }
            finally
            {
                SqlHelper.Close(command, reader);
            }
            return list;
        }
    }
}
